package services.db;

import java.sql.*;

/**
 * @author sergiu.marin
 * @since 26.01.2014
 */
public class TransferDataTask {

    private static void transferData(String tableName, int rowId, String primaryCol, Connection connectionOrigDB, Connection connectionCopyDB) throws SQLException {

        String query = "SELECT * FROM " + tableName + " WHERE " + primaryCol + "=?";
        String copyDBDelete = "DELETE FROM " + tableName + " WHERE " + primaryCol + "=?";
        String copyDBInsert = "INSERT INTO " + tableName + " VALUES(";
        PreparedStatement ps = connectionOrigDB.prepareStatement(query);
        ps.setInt(1, rowId);
        ResultSet origData = ps.executeQuery();

        ps = connectionCopyDB.prepareStatement(copyDBDelete);
        ps.setInt(1, rowId);
        ps.executeUpdate();

        for (int i = 1; i <= origData.getMetaData().getColumnCount(); i++) {
            if (i != 1) {
                copyDBInsert += ", ";
            }
            copyDBInsert += " ?";
        }
        copyDBInsert += ")";

        ps = connectionCopyDB.prepareStatement(copyDBInsert);

        if (origData.next()) {
            for (int i = 1; i <= origData.getMetaData().getColumnCount(); i++) {

                if (origData.getObject(i) instanceof String) {
                    ps.setString(i, origData.getString(i));
                } else if (origData.getObject(i) instanceof Integer) {
                    ps.setInt(i, origData.getInt(i));
                } else if (origData.getObject(i) instanceof Double) {
                    ps.setDouble(i, origData.getDouble(i));
                } else if (origData.getObject(i) instanceof Long) {
                    ps.setLong(i, origData.getLong(i));
                } else if (origData.getObject(i) instanceof Date) {
                    ps.setDate(i, origData.getDate(i));
                } else if (origData.getObject(i) instanceof Boolean) {
                    ps.setBoolean(i, origData.getBoolean(i));
                } else if (origData.getObject(i) instanceof byte[]) {
                    ps.setBlob(i, origData.getBlob(i));
                } else {
                    ps.setObject(i, origData.getClob(i));
                }
            }
        }
        ps.executeUpdate();


    }

    public void executeTask() {
        // get connection to change_tracker_db
        Connection connection = DBUtils.getConnection("localhost", "change_tracker_db", "root", "root");
        // get connection to the original db
        Connection connectionOrigDB = DBUtils.getConnection("localhost", "company", "root", "root");
        // get connection to the copy of original external db
        Connection connectionCopyDB = DBUtils.getConnection("localhost", "company_copy", "root", "root");

        String sql = "SELECT c.*, t.table_name  FROM table_row_changes AS c JOIN db_tables AS t ON t.id=c.table_id WHERE changed=true LIMIT 100";
        try {

            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                transferData(rs.getString("table_name"), rs.getInt("row_id"), rs.getString("primary_column"), connectionOrigDB, connectionCopyDB);

                PreparedStatement ps = connection.prepareStatement("UPDATE table_row_changes set changed=false WHERE id=?");
                ps.setInt(1, rs.getInt("id"));
                ps.executeUpdate();
            }

        } catch (SQLException e) {
            play.Logger.debug("An error has occurred: " + e.getMessage());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
                if (connectionCopyDB != null) {
                    connectionCopyDB.close();
                }
                if (connectionOrigDB != null) {
                    connectionOrigDB.close();
                }
            } catch (SQLException e) {
                play.Logger.debug("An error has occurred: " + e.getMessage());
            }
        }
    }
}
